﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for UserDAO
/// </summary>
public class UserDAO:DBAccess
{
	 #region sql Commands   
    string SQL_SELECT_ALL = "select * from [User]";
    string SQL_SELECT_BY_ID = "select * from [User] where ID=@id";
    
    string SQL_INSERT = "insert into [User](Username,Password,RegisteredDate,LastLogin, Role,PersonID) values(@Username,@Password,@RegisteredDate,@LastLogin, @Role, @PersonID)";
    string SQL_CHANGE_PASS = "update [User] set password=@newPassword where username=@username and Password=@password";
   

    string SQL_UPDATE = "update [User] set Username = @Username, Password = @Password, RegisteredDate = @RegisteredDate, LastLogin = @LastLogin, Role = @Role, PersonID = @PersonID where ID = @id";
    string SQL_DELETE = "delete from [User] where ID = @id";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public UserDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methos
    public User GetUserById(int id)
    {
        User user = new User();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    user.Id = dr.GetInt32(0);
                    user.UserName = dr.GetString(1);
                    user.Password = dr.GetString(2);
                    user.RegisteredDate = dr.GetDateTime(3);
                    user.LastLogin = dr.GetDateTime(4);
                    user.Role = dr.GetInt32(5);
                    user.PersonInfor.Id = dr.GetInt32(6);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return user;
    }
    /// <summary>
    /// GetUser username and password
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public User GetUser(string username, string password)
    {
        User user = new User();
        try
        {
            SqlCommand command = new SqlCommand("Select * from [User] where Username=@username and Password = @password", conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@username", username);
                command.Parameters.AddWithValue("@password", password);
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    user.Id = dr.GetInt32(0);
                    user.UserName = dr.GetString(1);
                    user.Password = dr.GetString(2);
                    user.RegisteredDate = dr.GetDateTime(3);
                    user.LastLogin = dr.GetDateTime(4);
                    user.Role = dr.GetInt32(5);
                    user.PersonInfor.Id = dr.GetInt32(6);                    
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return user;
    }

    /// <summary>
    /// User  Authorization 
    /// </summary>
    /// <param name="username"></param>
    /// <param name="password"></param>
    /// <returns></returns>
    public bool CheckAccount(string username, string password)
    {
        try
        {
            SqlCommand command = new SqlCommand("select * from Users where Username=@username and Password=@password", conn);
            command.Parameters.AddWithValue("@username", username);
            command.Parameters.AddWithValue("@password", password);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    Disconnect(); return true;
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return false;
    }

    /// <summary>
    /// Insert new user into database
    /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public int Insert(User user)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@Username", user.UserName);
            command.Parameters.AddWithValue("@Password", user.Password);
            command.Parameters.AddWithValue("@RegisteredDate", user.RegisteredDate);
            command.Parameters.AddWithValue("@LastLogin", user.LastLogin);
            command.Parameters.AddWithValue("@Role", user.Role);
            command.Parameters.AddWithValue("@PersonID", user.PersonInfor.Id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                //command.CommandText = "SELECT @@IDENTITY from User";
                //user.Id = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Update User
    /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public int Update(User user)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@Username", user.UserName);
            command.Parameters.AddWithValue("@Password", user.Password);
            command.Parameters.AddWithValue("@RegisteredDate", user.RegisteredDate);
            command.Parameters.AddWithValue("@LastLogin", user.LastLogin);
            command.Parameters.AddWithValue("@Role", user.Role);
            command.Parameters.AddWithValue("@PersonID", user.PersonInfor.Id);
            command.Parameters.AddWithValue("@id", user.Id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Delete user out of database
    /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public int Delete(int id)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id", id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Check if a person has had an account
    /// </summary>
    /// <param name="perId"></param>
    /// <returns></returns>
    public User CheckOwner(int perId)
    {
        User user = new User();
        try
        {
            SqlCommand cmd = new SqlCommand("SELECT ID, Username, RegisteredDate from [User] where PersonID =@perId",conn);
            cmd.Parameters.AddWithValue("@perId",perId);
            if (Connect())
            {
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    user.Id = dr.GetInt32(0);
                    user.UserName = dr.GetString(1);
                    user.RegisteredDate = dr.GetDateTime(2);
                }
            }
        }
        catch (SqlException ex)
        {
            message = ex.Message;
        }
        catch (Exception ex)
        {
            message = ex.Message;
        }
        return user;
    }
    /// <summary>
    /// Method to change password
    /// </summary>
    /// <param name="username">username</param>
    /// <param name="oldPass">current password</param>
    /// <param name="newPass">new password</param>
    /// <returns>number greater than 0 if action is successfull, else return 0</returns>
    public int ChangePassword(string username,string  oldPass, string newPass)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_CHANGE_PASS,conn);
            command.Parameters.AddWithValue("@newPassword",newPass);
            command.Parameters.AddWithValue("@Password",oldPass);
            command.Parameters.AddWithValue("@username",username);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex)
        {
            message = ex.Message;
        }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Get number of account in system
    /// </summary>
    /// <returns></returns>
    public int GetAccountCount()
    {
        int count = 0;
        try
        {
            SqlCommand command = new SqlCommand("select count(ID) from [User]", conn);            
            
            if (Connect())
            {
                count =int.Parse (command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex)
        {
            message = ex.Message;
        }
        catch (Exception ex) { message = ex.Message; }
        return count;
    }

    public IList<User> GetUserList(int currentPage, int pageSize, ref int total, string name)
    {
        IList<User> list = new List<User>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectUsers", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@name", SqlDbType.NVarChar);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@name"].Value = name;           
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    User user = new User();
                    user.Id = dr.GetInt32(1);
                    user.UserName = dr.GetString(2);
                    user.Role = dr.GetInt32(3);
                    user.RegisteredDate = dr.GetDateTime(4);
                    user.PersonInfor.FirstName = dr.GetString(5);
                    user.PersonInfor.LastName = dr.GetString(6);
                    list.Add(user);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    #endregion
}